SQL: Add & modify data

Easiest and commonly used method

Method to add or modify a table's data

1. Select the database in the left menu, then select the table to be modified.
2. Click on the "SQL" tab
3. Click on the buttons below the textarea: SELECT *, SELECT, INSERT, UPDATE or DELETE depending on your needs.

Syntax rules:

• Queries are separated by semicolons ";"
• Values are separated by commas ","
  • Strings: always surround by double quotes OR simple quotes (both work fine)
  • Numbers: no need of simple nor double quotes
  • Floats: use a point before decimals (no comma, unlike we do in france)
  • DATE and DATETIME : are in US format Y/m/d h:i:s. You can either use "/" or "-" as separators for the date, MySQL will automatically understand that the values are separated.

Conditional operators / selectors

• OR, AND,...
  • We use brackets to prioritize conditions.
  • Example: SELECT * FROM users WHERE city = "Paris" AND (firstname = "john OR firstname = "marc");
• IN (array): in case of complex conditions, instead of using series of OR, we can use this syntax: SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);It works for both numbers and strings.
• Equals to, Different from, ...
  • Equals to: with = sign
  • different from: with <!= (or <>) signs
  • Bigger than / Smaller than: >, >= / <, <=
• LIMIT 2, 4 means "only 4 lines, starting on line 2". Be carefull: First line is ranked 0, so when we write 2 as a strating point, the query will return from the 3rd line of the table (line 2 = 3rd line).
• When we need to select all fields for a query: instead of listing them all, the magic character * will allows to select all. Example: SELECT * FROM users will select all fields of the "users" table. Warning: This has a performance downturn. That's why it's recommanded to define each field one by one if possible.

If using Command lines

1. INSERT TO : key points / video
2. DELETE FROM : key points / video
3. UPDATE : key points / video
4. SELECT : key points / video

INSERT INTO table_name (field1, field2) VALUES   
    ("value1_line1", "value2_line1"),
    ("value1_line2", "value2_line2");
Exemple:
    INSERT INTO users (firstname, surname) VALUES   
        ('Dwayne', 'Johnson'),
        ('Will', 'Smith');

DELETE FROM table_name WHERE condition(s) [LIMIT number_of_lines];

Examples:
   
    DELETE FROM users WHERE id = "4";
        // Will delete the user whose id is 4.

    DELETE FROM users WHERE firstname = "Dayne" LIMIT 1;
        // Will delete only the first line in which firstname is set to "Dayne"

TRUNCATE TABLE table_name

Example:

    TRUNCATE TABLE users

DELETE FROM users WHERE id = 1 OR id = 3;
    // Will remove users whose ids are equals to 1 or 3

DELETE FROM users WHERE town = "Paris" AND age < 18 ;
    // Will remove all users under 18 years old living in Paris

UPDATE table_name SET field1=value1 [, field2=value2, ...] WHERE condition(s);

Examples:

    UPDATE table_name SET town="Paris", gender="h" WHERE id = 4 OR id = 6;
        // Will update "town" and "gender" values for users whose ids are either 4 and 6

UPDATE users SET surname = firstname ; 
ALTER TABLE users DROP firstname ;
    // Will change the last name to the first name value for all users (then delete the first name column)

SELECT field1 [field2, ...] 
    FROM database_name WHERE condition(s) 
    LIMIT number_of_lines

Example:

    SELECT surname, firstname FROM users LIMIT 2, 5;
    
    // Will get values for "surname" and "firstname" fields for lines 2 to 5 in "users" table

    SELECT column1 [, column2, ...] 
        FROM table_name 
        [ ORDER BY column1 [ASC/DESC] [, column2, ...] ]

Example:
    
    SELECT * 
        FROM users 
        ORDER BY surname DESC, id DESC

    // Will return all users, decreasingly sorted based on the "surname" column (so, form A to Z in this case as the column is set on VARCHAR)
    // And if several lines have the same value for "surname", then these will be decreasingly sorted base on their "id" (so from 0 to 65,536 as id is set to SMALLINT)

SELECT COUNT(target_column1 [, target_column2, ...]) AS choose_a_name 
    FROM table_name
    
Example:

    SELECT COUNT(id) AS population 
        FROM users
        
    // Will return an integers (example: 4) which corresponds to the total number of lines in the "users" table
    // Output : 'population' => 4

Example:
SELECT COUNT(id) AS pouplation, gender
    FROM users
    GROUP BY gender;
    
// Output: 'female' => 1, 'male' => 3

SELECT * FROM users WHERE surname LIKE "J%";
    // Will return a list of users whose surname starts with "J"

SELECT * FROM users WHERE firstname LIKE "%h%"
    // Will return a list of users whose firstname contains the letter "h"
    
SELECT * FROM users WHERE email LIKE "%gmail.com"
    // Will return a list of users who are using Gmail as an email service provider